﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

public partial class CrystalReport_ImplementSituation : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            BindDate();
        if (Session["UserStreet"] != null)
        {
            DataSetCrystal dsc = new DataSetCrystal();
            string connString = ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString;
            //string time = ddlyear.SelectedValue + "-" + ddlmonth.SelectedValue + "-1 00:00:00";
            int time = Convert.ToInt32(ddlyear.SelectedValue) * 100 + Convert.ToInt32(ddlmonth.SelectedValue);
            string cmdtext = "";
            string cmdtext1="";
            string cmdtext2="";
            string relation="";
            string[] option = new string[2] {">0","<0" };
            for (int i = 0; i < 2; i++)
            {
                float subnum = 0;
                float submoney = 0;
                float rednum = 0;
                float redmoney = 0;
                float kindnum = 0;
                float kindarea = 0;
                if (Session["UserStreet"].ToString() == "海曙")
                {
                    //Subsidy
                    cmdtext = @"SELECT COUNT(*),SUM(Sub.Mon) FROM (SELECT DISTINCT adj_longid AS ID,adj_nowmoney AS Mon
                                FROM SubsidyRecord WHERE adj_starttime<='" + time + "' AND adj_moneychange" + option[i] + " AND adj_stoptime>'" + time + "') AS Sub";
                    SqlDataReader drsub = SqlHelper.ExecuteReader(connString, CommandType.Text, cmdtext);
                    if (drsub.Read())
                    {
                        subnum = float.Parse(string.IsNullOrEmpty(drsub[0].ToString())?"0":drsub[0].ToString());
                        submoney = float.Parse(string.IsNullOrEmpty(drsub[1].ToString()) ? "0" : drsub[1].ToString());
                    }
                    drsub.Close();
                    //Reduce
                    cmdtext1 = @"SELECT COUNT(*),SUM(Red.Mon) FROM (SELECT DISTINCT adj_longid AS ID,adj_nowmoney AS Mon
                                FROM ReduceRecord WHERE adj_starttime<='" + time + "' AND adj_reducechange"+option[i]+" AND adj_stoptime>='" + time + "') AS Red";
                    SqlDataReader drred = SqlHelper.ExecuteReader(connString, CommandType.Text, cmdtext1);
                    if (drred.Read())
                    {
                        rednum = float.Parse(string.IsNullOrEmpty(drred[0].ToString()) ? "0" : drred[0].ToString());
                        redmoney = float.Parse(string.IsNullOrEmpty(drred[1].ToString()) ? "0" : drred[1].ToString());
                    }
                    drred.Close();
                    //Kind
                    cmdtext2 = @"SELECT COUNT(*),SUM(Kind.Area) FROM (SELECT DISTINCT adj_longid AS ID,adj_nowusearea AS Area
                                FROM KindRecord WHERE adj_starttime<='" + time + "' AND adj_stoptime>='" + time + "') AS Kind";
                    SqlDataReader drkind = SqlHelper.ExecuteReader(connString, CommandType.Text, cmdtext2);
                    if (drkind.Read())
                    {
                        kindnum = float.Parse(string.IsNullOrEmpty(drkind[0].ToString()) ? "0" : drkind[0].ToString());
                        kindarea = float.Parse(string.IsNullOrEmpty(drkind[1].ToString()) ? "0" : drkind[1].ToString());
                    }
                    drkind.Close();
                }
                else
                {
                    //Subsidy
                    cmdtext = @"SELECT COUNT(*),SUM(Sub.Mon) FROM (SELECT DISTINCT adj_longid AS ID,adj_nowmoney AS Mon
                                FROM SubsidyRecord WHERE adj_starttime<='" + time + "' AND adj_moneychange" + option[i] + " AND adj_stoptime>='" + time + "') AS Sub";
                    SqlDataReader drsub = SqlHelper.ExecuteReader(connString, CommandType.Text, cmdtext);
                    if (drsub.Read())
                    {
                        subnum = float.Parse(string.IsNullOrEmpty(drsub[0].ToString()) ? "0" : drsub[0].ToString());
                        submoney = float.Parse(string.IsNullOrEmpty(drsub[1].ToString()) ? "0" : drsub[1].ToString());
                    }
                    drsub.Close();
                    //Reduce
                    cmdtext1 = @"SELECT COUNT(*),SUM(Red.Mon) FROM (SELECT DISTINCT adj_longid AS ID,adj_nowmoney AS Mon
                                FROM ReduceRecord WHERE adj_starttime<='" + time + "' AND adj_reducechange" + option[i] + " AND adj_stoptime>='" + time + "') AS Red";
                    SqlDataReader drred = SqlHelper.ExecuteReader(connString, CommandType.Text, cmdtext1);
                    if (drred.Read())
                    {
                        rednum = float.Parse(string.IsNullOrEmpty(drred[0].ToString()) ? "0" : drred[0].ToString());
                        redmoney = float.Parse(string.IsNullOrEmpty(drred[1].ToString()) ? "0" : drred[1].ToString());
                    }
                    drred.Close();
                    //Kind
                    cmdtext2 = @"SELECT COUNT(*),SUM(Kind.Area) FROM (SELECT DISTINCT adj_longid AS ID,adj_nowusearea AS Area
                                FROM KindRecord WHERE adj_starttime<='" + time + "' AND adj_stoptime>='" + time + "') AS Kind";
                    SqlDataReader drkind = SqlHelper.ExecuteReader(connString, CommandType.Text, cmdtext2);
                    if (drkind.Read())
                    {
                        kindnum = float.Parse(string.IsNullOrEmpty(drkind[0].ToString()) ? "0" : drkind[0].ToString());
                        kindarea = float.Parse(string.IsNullOrEmpty(drkind[1].ToString()) ? "0" : drkind[1].ToString());
                    }
                    drkind.Close();
                }
                DataTable dt = dsc.Tables["ImplementSituation"];
                DataRow datar = dt.NewRow();
                if (i == 0)
                    datar[0] = "当月增加";
                else
                    datar[0] = "当月减少";
                datar[1] = subnum;
                datar[2] = submoney;
                datar[3] = rednum;
                datar[4] = redmoney;
                datar[5] = kindnum;
                datar[6] = kindarea;
                datar[7] = subnum + rednum + kindnum;
                datar[8] = submoney + redmoney;
                datar[9] = kindarea;
                dt.Rows.Add(datar);
            }
            //使用报表对象加载报表
            ReportDocument myReport = new ReportDocument();
            string reportPath = Server.MapPath("~/CrystalReport/ImplementSituation.rpt");
            myReport.Load(reportPath);
            //绑定数据集，注意，一个报表用一个数据集。
            myReport.SetDataSource(dsc);


            CrystalReportViewer1.ReportSource = myReport;
            ParameterFields paramFields = new ParameterFields();
            ParameterField paramField = new ParameterField();
            ParameterDiscreteValue pdValue = new ParameterDiscreteValue();

            paramField.ParameterFieldName = "Time";
            pdValue.Value = ddlyear.SelectedValue + "年" + ddlmonth.SelectedValue + "月份";
            paramField.CurrentValues.Add(pdValue);
            paramFields.Add(paramField);

            CrystalReportViewer1.ParameterFieldInfo = paramFields;
        }
        else
            WebHelper.AlertAndRedirect("登陆超时，请重新登陆！","../Login.aspx");
    }
    public void BindDate()
    {
        int year = Convert.ToInt32(DateTime.Now.Year.ToString());
        for (int i = year - 5; i < year + 1; i++)
        {
            ddlyear.Items.Add(i.ToString());
        }
        ddlyear.SelectedValue = DateTime.Now.Year.ToString();
        ddlmonth.SelectedValue = DateTime.Now.Month.ToString();
    }
}
